<?php

require ('config.php');

// filtering sql query for easy troubleshooting
function safe_query($query = "") {
	if(empty($query)){
		return false;
	}
	$result = mysql_query($query);
	if ($result) {
	  return $result;
	} else {
	  $log = fopen("failquery.log", "ab");
	  $recDate = date("d-m-Y H:i");
	  $loginfo = "[$recDate] query failed: errorno=".mysql_errno().";error=".mysql_error().";\nquery=".$query."\n";
	  fwrite($log, $loginfo);
	  fclose($log);
	  return FALSE;
	}
}

// query untuk cek login
function getLogin($username, $password) {
	$query = "SELECT * FROM `user` WHERE `username` = '".$username."' AND `password` = '".$password."'";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data operator
function getOperator() {
	
	$query = "SELECT *,pegawai.nama_pegawai FROM `user` 
			  JOIN `pegawai` ON user.idpegawai = pegawai.idpegawai
			  WHERE `hak_akses` = 'Operator'";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data pegawai
function getPegawai() {
	$query = "SELECT * FROM `pegawai`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

function addUser($username, $name, $email, $pass, $level,$sektoral) {
    $query = "INSERT INTO `user` (`username`, `password`, `idpegawai`, `email`, `hak_akses`, `sektoral`)
			  VALUES ('".$username."', '".$pass."', '".$name."', '".$email."', '".$level."', '".$sektoral."' )";
			  
	$result = safe_query($query);
	if ($result && (mysql_affected_rows()>0))
		return TRUE;
	else
		return FALSE;
}


// update progress monitoring
function updateMonitoring($idkegiatan, $picName, $progress, $date) {
    $query = "INSERT INTO `monitoring` (`idmonitoring`, `idkegiatan`, `tgl_monitoring`, `progres`, `foto`)
			  VALUES ('','".$idkegiatan."', '".$date."', '".$progress."', '".$picName."')";
			  
	$result = safe_query($query);
	if ($result && (mysql_affected_rows()>0))
		return TRUE;
	else
		return FALSE;
}

// menampilkan data user berdasarkan ud
function getUserbyID($id) {
	$query = "SELECT *, pegawai.nama_pegawai FROM `user` 
			  JOIN `pegawai` ON user.idpegawai = pegawai.idpegawai
			  WHERE `username` = '".$id."'";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data kabupaten
function getKab() {
	$query = "SELECT * FROM `kabkota` ORDER BY `idkabkota`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data kriteria
function getKriteria() {
	$query = "SELECT * FROM `kriteria` ORDER BY `idkriteria`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data alternatif
function getAlternatif() {
	$query = "SELECT * FROM `alternatif` ORDER BY `idalternatif`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data sektoral
function getSektoral() {
	$query = "SELECT * FROM `sektoral` ORDER BY `idsektoral`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

function addSektoral($nama_sektoral, $ikon) {
    $query = "INSERT INTO `sektoral` (`nama_sektoral`, `icon` ) VALUES ('".$nama_sektoral."', '".$ikon."')";
	$result = safe_query($query);
	if ($result && (mysql_affected_rows()>0))
		return TRUE;
	else
		return FALSE;
}

// menampilkan data kegiatan
function getKegiatan() {
	$query = "SELECT kegiatan.*, kabkota.nama_kabkota as `kabupaten`, sektoral.nama_sektoral as `sektoral` FROM `kegiatan` 
					  JOIN `kabkota`  ON kabkota.idkabkota = kegiatan.idkabkota
					  JOIN `sektoral` ON sektoral.idsektoral = kegiatan.idsektoral
					  ORDER BY `idkegiatan`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data kegiatan berdasarkan ID
function getKegiatanbyID($id) {
	$query = "SELECT kegiatan.*, kabkota.nama_kabkota as `kabupaten`,kabkota.idkabkota, 
					  sektoral.nama_sektoral as `sektoral`, sektoral.idsektoral, sektoral.icon
					  FROM `kegiatan` 
					  JOIN `kabkota`  ON kabkota.idkabkota = kegiatan.idkabkota
					  JOIN `sektoral` ON sektoral.idsektoral = kegiatan.idsektoral WHERE `idkegiatan` = '".$id."'
					  ORDER BY `idkegiatan`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data kegiatan berdasarkan id kabupaten
function getListKegiatanByKabupaten($id) {
	$query = "SELECT kegiatan.*, kabkota.nama_kabkota as `kabupaten`,kabkota.idkabkota, 
					  sektoral.nama_sektoral as `sektoral`, sektoral.idsektoral FROM `kegiatan` 
					  JOIN `kabkota`  ON kabkota.idkabkota = kegiatan.idkabkota
					  JOIN `sektoral` ON sektoral.idsektoral = kegiatan.idsektoral WHERE kegiatan.idkabkota = '".$id."'
					  ORDER BY `idkegiatan`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data kegiatan berdasarkan id sektoral
function getListKegiatanBySektoral($id) {
	$query = "SELECT kegiatan.*, kabkota.nama_kabkota as `kabupaten`,kabkota.idkabkota, 
					  sektoral.nama_sektoral as `sektoral`, sektoral.idsektoral FROM `kegiatan` 
					  JOIN `kabkota`  ON kabkota.idkabkota = kegiatan.idkabkota
					  JOIN `sektoral` ON sektoral.idsektoral = kegiatan.idsektoral WHERE kegiatan.idsektoral = '".$id."'
					  ORDER BY `idkegiatan`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data monitor berdasarkan id kegiatan
function getKegiatanMonitor($id) {
	$query = "SELECT * from `monitoring` WHERE `idkegiatan` = '".$id."' ORDER BY `progres`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}

// menampilkan data monitor berdasarkan id kegiatan
function getProgress($id) {
	$query = "select max(progres) as `maks` from `monitoring` where idkegiatan = '".$id."'";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}


function addKegiatan($nama_kegiatan, $sektor, $kabkota, $pagu, $longitude, $latitude,$polygon, $tahun, $username) {
    $query = "INSERT INTO `kegiatan` (`idkegiatan`, `nama_kegiatan`, `idkabkota`, `pagu`, `lat`, `long`,`polygon`, `tahun`, `idsektoral`, `username` )
			  VALUES ('','".$nama_kegiatan."', '".$kabkota."', '".$pagu."', '".$latitude."', '".$longitude."', '".$polygon."', '".$tahun."', '".$sektor."', '".$username."')";
			  
	$result = safe_query($query);
	if ($result && (mysql_affected_rows()>0))
		return TRUE;
	else
		return FALSE;
}

function deleteKegiatan($id) {
    $query = "DELETE FROM `kegiatan` WHERE `idkegiatan` = '".$id."'";	  
	$result = safe_query($query);
	if ($result && (mysql_affected_rows()>0))
		return TRUE;
	else
		return FALSE;
}

function deleteMonitoring($id) {
    $query = "DELETE FROM `monitoring` WHERE `idkegiatan` = '".$id."'";		  
	$result = safe_query($query);
	if ($result && (mysql_affected_rows()>0))
		return TRUE;
	else
		return FALSE;
}

/* function untuk menampilkan data buku tamu */
function getBukuTamu() {
	$query = "SELECT * FROM `bukutamu`";
	$result = safe_query($query);
	if(($result) && (mysql_num_rows($result) > 0)) {
		$data = array();
		while($row = mysql_fetch_assoc($result))
		$data[] = $row;
		return $data;
	} else {
		return FALSE;
	}
}
